﻿using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WCS.Utils;

namespace WCS.Helpers
{
    public static class SQLHelper
    {
        public static DataTable QueryStr(this string str)
        {
            if (str.Contains("<300"))
                LogHelper.WriteError(str);

            using (SqlSugarClient db = DBContext.GetInstance())
            {
                return db.Ado.GetDataTable(str);
            }
        }

        public static DataTable QueryStrCount(this string str,out int count)
        {
            using (SqlSugarClient db = DBContext.GetInstance())
            {
                DataTable dt = db.Ado.GetDataTable(str);
                count = dt.Rows.Count;
                return dt;
            }
        }

        public static DataTable QueryPageList(this string tableName, string columes, string sqlwhere, int begin, int end, out int count)
        {
            using (SqlSugarClient db = DBContext.GetInstance())
            {
                string sql = $@"SELECT count(*) as count FROM {tableName} WHERE 1=1 {sqlwhere}";
                count = (int)db.Ado.GetDataTable(sql).Rows[0]["count"];

                sql = $@"select * from
                            (SELECT ROW_NUMBER() over(order by seqId) 序号,{columes} FROM {tableName} WHERE 1=1 {sqlwhere}) t
                         where t.序号 between {begin} and {end}";
                DataTable dt = db.Ado.GetDataTable(sql);
                return dt;
            }
        }

        public static int ExecuteStr(this string str)
        {
            using (SqlSugarClient db = DBContext.GetInstance())
            {
                return db.Ado.ExecuteCommand(str);
            }
        }

        /// <summary>
        /// 从DataTable种查数据，返回子DataTable
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static DataTable SreeenDataTable(this DataTable dt, string strWhere)
        {
            if (dt.Rows.Count <= 0) return dt;        //当数据为空时返回
            DataTable dtNew = dt.Clone();         //复制数据源的表结构
            DataRow[] dr = dt.Select(strWhere);  //strWhere条件筛选出需要的数据！
            for (int i = 0; i < dr.Length; i++)
            {
                dtNew.Rows.Add(dr[i].ItemArray);  // 将DataRow添加到DataTable中
            }
            return dtNew;
        }
    }
}
